/*
 * UWT 2014
 */
package database;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import controller.VisualCandoControls;

public final class PopulateCandoTable {
	private static final Logger logger = LoggerFactory.getLogger(PopulateCandoTable.class);
	
	/**
	 * Database connection.
	 */
	private final CandoDatabase db;
	
	public static void main(String[] args) {
		final CandoDatabase db = new CandoDatabase(new VisualCandoControls());
		final PopulateCandoTable popTable = new PopulateCandoTable(db);
		try {
			popTable.populateCandoTable(db.getDBConnection());
		} catch (NumberFormatException e) {
			logger.error("Number format error during population of cando table: {}", e);
			db.displayErrorMessage("Number format error during population of cando table: " + e.toString());
		} catch (IOException e) {
			logger.error("IO error during population of cando table: {}", e);
			db.displayErrorMessage("IO error during population of cando table: " + e.toString());
		} catch (SQLException e) {
			logger.error("SQL error during population of cando table: {}", e);
			db.displayErrorMessage("SQL error during population of cando table: " + e.toString());
		}
	}
	
	/**
	 * Constructs PopulateCandoTable object.
	 * 
	 * @param db Database connection.
	 */
	public PopulateCandoTable(final CandoDatabase db) {
		this.db = db;
	}
	
	/**
	 * Populates cando table with data.
	 * 
	 * @param conn SQL connection.
	 * @throws NumberFormatException Number format error.
	 * @throws IOException IO error.
	 * @throws SQLException SQL error.
	 */
	public void populateCandoTable(final Connection conn) throws NumberFormatException, IOException, SQLException {
		logger.info("Populationg cando table...");
		final int totalPartitions = this.db.getNumVerticalPartitions();
		final int numberCompounds = this.db.getNumCompounds();
		
		PreparedStatement[] preparedStatements = new PreparedStatement[totalPartitions];
		for (int i = 0; i < totalPartitions; i++) {
			preparedStatements[i] = getPreparedStatement(conn, i);
		}
		
		final BufferedReader bReader = new BufferedReader(new FileReader(this.db.getCandoFile()));		
		String line;
		int partitionRange = numberCompounds / totalPartitions + 2;
		conn.setAutoCommit(false);
		while ((line = bReader.readLine()) != null) {
			String values[] = line.split("\t");
			int rangeAdjust = 0;
			for (int i = 0; i < totalPartitions; i ++) {
				preparedStatements[i].setString(1, values[0]);
				final int proteinNumber = Integer.parseInt(values[1]);
				preparedStatements[i].setInt(2, proteinNumber);

				if (i == totalPartitions - 1) {
					rangeAdjust = 1;
				}
				for (int j = 3; j <= partitionRange + rangeAdjust; j++) {
					preparedStatements[i].setDouble(j, Double.parseDouble(values[j]));
				}
				preparedStatements[i].executeUpdate();
			}
		}
		conn.commit();
		bReader.close();	
		logger.info("Population of cando table with data COMPLETE.");
	}
	
	/**
	 * Creates SQL prepared statement for specified table partition.
	 *
	 * @param conn SQL connection.
	 * @param partitionNumber Partition number.
	 * @return Returns SQL prepared statement.
	 */
	private PreparedStatement getPreparedStatement(final Connection conn, final int partitionNumber) {
		final int numberCompounds = this.db.getNumCompounds();
		final int totalPartitions = this.db.getNumVerticalPartitions();
		final String tableName = this.db.getCandoTableName();
		final String columnOneName = CreateCandoTable.COLUMN_ONE_NAME;
		final String columnTwoName = CreateCandoTable.COLUMN_TWO_NAME;

		final int partitionRange = numberCompounds / totalPartitions;
		final int partitionStart = partitionNumber * partitionRange + 1;
		int partitionEnd = partitionNumber * partitionRange + partitionRange;

		if (partitionNumber == totalPartitions - 1) {
			partitionEnd += (numberCompounds % totalPartitions);
		}

		final StringBuilder columnNameList = new StringBuilder();
		final StringBuilder valuesList = new StringBuilder();
		columnNameList.append("`" + columnOneName);
		columnNameList.append("`, `" + columnTwoName);
		valuesList.append("VALUES (?, ?");

		for (int j = partitionStart; j <= partitionEnd; j++) {
			columnNameList.append("`, `" + j);
			valuesList.append(", ?");
		}
		columnNameList.append("`) ");
		valuesList.append(");");			
		String insertString = "INSERT INTO " + tableName + "_" + (partitionNumber + 1) + " (" +
				columnNameList.toString() + valuesList.toString();
		PreparedStatement insertPrepared = null;
		try {
			insertPrepared = conn.prepareStatement(insertString);
			logger.info("Created SQL prepared statement: {}", insertString);
		} catch (SQLException e) {
			logger.error("Error creating SQL prepared statement during population of cando table. Message: {}", e);
			this.db.displayErrorMessage("Error creating SQL prepared statement during population of cando table. Message: " + e.toString());
		}
		return insertPrepared;

	}

}
